package com.ids.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.List;
import com.ids.bean.TeamAttendanceData;
import com.ids.util.Constants;
import com.ids.util.DbManager;
import com.mysql.jdbc.Statement;

public class TeamAttendanceApprovalDao {

	private static TeamAttendanceApprovalDao instance = new TeamAttendanceApprovalDao();

	private List<String> results = new ArrayList<String>();

	public static TeamAttendanceApprovalDao getInstance() {
		return instance;
	}

	public List<String> insertApprovedEntries(List<TeamAttendanceData> teamAttendance) {
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		String insertApprovedAttendanceData = "update attendance set attendanceTypeId =?,dependantAttendanceTypeId = ? , statusId = ? , updationDate = ?  where empCode=? and dated =?";
		String empCode;
		int attendanceStatus;
		int attendanceTypeId;
		int dependantAttendanceTypeId;
		boolean isValidAttendance;
		double compOffCount;
		Date date;
		final int PENDING_ATTENDANCE = 1;
		Calendar cal = Calendar.getInstance();
		Date todaysDate = new Date(cal.getTime().getTime());
		try {
			PreparedStatement pstmtApprovedAttendanceData = conn
					.prepareStatement(insertApprovedAttendanceData);
			for (TeamAttendanceData teamAttendanceData : teamAttendance) {
				empCode = teamAttendanceData.getEmpCode();
				attendanceTypeId = teamAttendanceData.getAttendanceTypeId();
				dependantAttendanceTypeId = teamAttendanceData
						.getDependantAttendanceTypeId();
				attendanceStatus = teamAttendanceData.getStatusId();
				compOffCount = teamAttendanceData.getCompOffData();
				date = new Date(teamAttendanceData.getDated().getTime());
				if (attendanceStatus != PENDING_ATTENDANCE) {
					isValidAttendance = validateAttendanceTypes(
							attendanceTypeId, dependantAttendanceTypeId);
					if (isValidAttendance) {
						if (compOffCount != 0) {
						//	insertCompOffLeaves(empCode, compOffCount, date,conn);
							updateCompOffLeaves(empCode, compOffCount, date);
					}
					pstmtApprovedAttendanceData.setInt(1, attendanceTypeId);
					pstmtApprovedAttendanceData.setInt(2,dependantAttendanceTypeId);
					pstmtApprovedAttendanceData.setInt(3, attendanceStatus);
					pstmtApprovedAttendanceData.setDate(4, todaysDate);
					pstmtApprovedAttendanceData.setString(5, empCode);
					pstmtApprovedAttendanceData.setDate(6, date);
					updateLeaveBalance(attendanceTypeId, empCode);
					updateLeaveBalance(dependantAttendanceTypeId, empCode);
					pstmtApprovedAttendanceData.addBatch();

					
			} else {
				results.add("Invalid Attendance for emp-code "
						+ empCode + " and date " + date + "<br>");
			}
		}
			}
			int[] batchResult = pstmtApprovedAttendanceData.executeBatch();
			if (Arrays.toString(batchResult).contains("1")) {
				if (results.isEmpty()) {
					results.add("Attendance has been updated successfully<br>");
				} else {
					results
							.add("Partial attendance has been updated successfully<br>");
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			results.add("Attendance updation failed<br>");
		} finally {
			DbManager.closeConnection(conn);
		}
		return results;
	}

	public void updateLeaveBalance(int attendanceTypeId, String empCode) {
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		double deduction = 0.0;
		String leaveType = "";

		switch (attendanceTypeId) {
		case Constants.ATTENDANCE_ONE_CL:
			leaveType = "CL";
			deduction = 1.0;
			break;
		case Constants.ATTENDANCE_HALF_CL:
			leaveType = "CL";
			deduction = 0.5;
			break;
		case Constants.ATTENDANCE_ONE_SL:
			leaveType = "SL";
			deduction = 1.0;
			break;
		case Constants.ATTENDANCE_HALF_SL:
			leaveType = "SL";
			deduction = 0.5;
			break;
		case Constants.ATTENDANCE_ONE_PL:
			leaveType = "PL";
			deduction = 1.0;
			break;
		case Constants.ATTENDANCE_COMP_OFF:
			leaveType = "CO";
			deduction = 1.0;
			break;
		case Constants.ATTENDANCE_HALF_COMP_OFF:
			leaveType = "CO";
			deduction = 0.5;
			break;
		}

		String leaveUpdateSQL = "update leaveCount set count = count-"
				+ deduction + " where empCode=" + empCode;
		if (!leaveType.equals("")) {
			leaveUpdateSQL += " and leaveType='" + leaveType + "'";
		}
		try {
			Statement stmt = (Statement) conn.createStatement();
			stmt.execute(leaveUpdateSQL);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbManager.closeConnection(conn);
		}

	}

	//	public void insertCompOffLeaves(String empCode, double count, Date date,Connection conn) {
	//		String insertCompOffLeavesData = "insert into leaves (empCode,count,dated,attendanceId) values ( ?,?,?,(select attendanceTypeId from attendancetype  where attendancetype=Upper('Comp off')))";
	//		try {
	//			PreparedStatement pstmtCompOffLeavesData = conn
	//					.prepareStatement(insertCompOffLeavesData);
	//			pstmtCompOffLeavesData.setString(1, empCode);
	//			pstmtCompOffLeavesData.setDouble(2, count);
	//			pstmtCompOffLeavesData.setDate(3, date);
	//			pstmtCompOffLeavesData.execute();
	//		} catch (SQLException e) {
	//			e.printStackTrace();
	//			results.add("Comp-Off updation failed for emp-code " + empCode
	//					+ " and date " + date + "<br>");
	//		}
	//	}
	public void updateCompOffLeaves(String empCode, double compOffCount,
			Date date) {
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		String leaveType = "CO";
		String updateCompOffLeavesSql = "update leaveCount set count = count +"
				+ compOffCount + " where leaveType = '" + leaveType
				+ "' and  empCode=" + empCode;
		try {
			Statement updateCompOffLeavesStmt = (Statement) conn.createStatement();
			updateCompOffLeavesStmt.execute(updateCompOffLeavesSql);

		} catch (SQLException e) {
			e.printStackTrace();
			results.add("Comp-Off updation failed for emp-code " + empCode
			+ " and date " + date + "<br>");
		}
	}

	private boolean validateAttendanceTypes(int attendanceTypeId,
			int dependentAttendanceTypeId) {
		String attendanceTypeSql = "select * from  dependantattendancetype   where attendanceType="
				+ attendanceTypeId
				+ " and dependantType="
				+ dependentAttendanceTypeId;
		return getAttendanceTypeList(attendanceTypeSql);

	}

	private boolean getAttendanceTypeList(String attendanceTypeSql) {
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		ResultSet attendanceTypeRS = null;
		Statement smt = null;
		boolean isAuthentic = false;
		try {
			smt = (Statement) conn.createStatement();
			attendanceTypeRS = smt.executeQuery(attendanceTypeSql);
			while (attendanceTypeRS.next()) {
				isAuthentic = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return isAuthentic;
	}
	

}
